package edu.cmu.aes.project2.wshelper;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import edu.cmu.aes.share.MySqlProperties;

public class Billing {

	private static String databaseLocation = MySqlProperties.getURL()+"/aes_project2_billing";
	private static String userName = MySqlProperties.getUserName();
	private static String password = MySqlProperties.getPassword();

	// public static void main(String args[]){
	// Billing billing = new Billing();
	//
	// Calendar cal = Calendar.getInstance();
	// java.sql.Date date = new java.sql.Date(cal.getTime().getTime());
	//
	// billing.payBill("prasanna",5000,date);
	// ArrayList<Invoice> array = billing.getInvoice("prasanna");
	// for(int i=0;i<array.size();i++){
	// System.out.println(((Invoice)array.get(i)).getAmount());
	// System.out.println(((Invoice)array.get(i)).getCustomer_name());
	// System.out.println(((Invoice)array.get(i)).getPaid_date());
	// }
	// }

	private static Connection getConnection() throws SQLException {
		Connection connection = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(databaseLocation,
					userName, password);
		} catch (Exception e) {
			throw new SQLException("Cannot instantiate database connector");
		}
		return connection;
	}

	public boolean payBill(Order order) {
		int choice = (int)(Math.random()*2);

		java.sql.Date sqlDate = new java.sql.Date(order.getDate().getTime());
		if (choice == 0)
			return false;
		else {

			try {
				Connection conn = getConnection();

				PreparedStatement pstmnt = conn
						.prepareStatement("insert into invoice(customer_name,amount,paid_date) values(?,?,?)");

				pstmnt.setString(1, order.getCustomerName());
				pstmnt.setDouble(2, order.getAmount());
				pstmnt.setDate(3, sqlDate);
				int result = pstmnt.executeUpdate();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return true;
		}
	}

	public boolean payBillRollBack(Order order) {

		java.sql.Date sqlDate = new java.sql.Date(order.getDate().getTime());
		try {
			Connection conn = getConnection();

			PreparedStatement pstmnt = conn
					.prepareStatement("delete from invoice where customer_name = ? and amount = ? and paid_date = ?");

			pstmnt.setString(1, order.getCustomerName());
			pstmnt.setDouble(2, order.getAmount());
			pstmnt.setDate(3, sqlDate);
			int result = pstmnt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;

	}

	public ArrayList<Invoice> getInvoice(String customer_name) {
		ArrayList<Invoice> invoicearray = new ArrayList<Invoice>();
		Invoice invoice;
		try {
			Connection conn = getConnection();
			PreparedStatement pstmnt = conn
					.prepareStatement("select * from invoice where customer_name = ? order by invoice_id");
			pstmnt.setString(1, customer_name);
			ResultSet result = pstmnt.executeQuery();
			while (result.next()) {
				invoice = new Invoice();
				invoice.setAmount(result.getDouble("amount"));
				invoice.setCustomer_name(result.getString("customer_name"));
				invoice.setPaid_date(result.getDate("paid_date"));
				invoicearray.add(invoice);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return invoicearray;
	}

}
